﻿using log4net;
using NLog.Internal;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.Linq;

namespace TMSProgram
{
    public partial class Form1 : Form
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(Form1));
        private int pageSize = 50; // 每页显示的行数
        private int currentPage = 1; // 当前页码
        private int totalPages = 0; // 总数
        string connStr = ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString;
        private DataGridView dataGridView1;
        private Button btnQuery;
        private Button btnLastPage;
        private Button btnNextPage;
        private Button exportBtn;
        private Button setBtn;
        private Label txtPage;
        private Label meterNumText;
        private Label jianText;
        private Label dateText;
        private TextBox startMeterNum;
        private TextBox endMeterNum;
        private TextBox startJianNo;
        private TextBox endJianNo;
        private DateTimePicker dtp;
        private DateTimePicker dtp2;
        private SqlConnection conn;
        private DataTable dt;

        //设置UI
        private void SetupUI()
        {
            FlowLayoutPanel panel = new FlowLayoutPanel
            {
                Dock = DockStyle.Bottom,
                WrapContents = false, // 禁止换行
                AutoSizeMode = AutoSizeMode.GrowAndShrink,
                FlowDirection = FlowDirection.RightToLeft,
                Padding = new Padding(20, 50, 0, 0)
            };
            meterNumText = new Label
            {
                Location = new Point(20, 13),
                Size = new Size(85, 20),
                Text = "出厂表号范围:"
            };
            // 出厂表号
            startMeterNum = new TextBox
            {
                Location = new Point(105, 10),
                Size = new Size(120, 20),
                Text = ""
            };
            Label flag = new Label
            {
                Location = new Point(230, 16),
                Size = new Size(10, 20),
                Text = "~"
            };
            // 出厂表号
            endMeterNum = new TextBox
            {
                Location = new Point(245, 10),
                Size = new Size(120, 20),
                Text = ""
            };

            jianText = new Label
            {
                Location = new Point(370, 13),
                Size = new Size(85, 20),
                Text = "检验编号范围:"
            };
            // 检验编号
            startJianNo = new TextBox
            {
                Location = new Point(455, 10),
                Size = new Size(120, 20),
                Text = ""
            };
            Label flag2 = new Label
            {
                Location = new Point(580, 16),
                Size = new Size(10, 20),
                Text = "~"
            };
            // 检验编号
            endJianNo = new TextBox
            {
                Location = new Point(595, 10),
                Size = new Size(120, 20),
                Text = ""
            };

            dateText = new Label
            {
                Location = new Point(20, 43),
                Size = new Size(85, 20),
                Text = "检验日期范围:"
            };
            dtp = new DateTimePicker
            {
                Location = new Point(105, 40),
                Size = new Size(120, 20),
                Format = DateTimePickerFormat.Custom,
                CustomFormat = "yyyy-MM-dd",  // 自定义时间格式
                Visible = true,
                ShowUpDown = true,
                Value = DateTime.Now
            };
            Label flag3 = new Label
            {
                Location = new Point(230, 50),
                Size = new Size(10, 20),
                Text = "~"
            };
            dtp2 = new DateTimePicker
            {
                Location = new Point(245, 40),
                Size = new Size(120, 20),
                Format = DateTimePickerFormat.Custom,
                CustomFormat = "yyyy-MM-dd",  // 自定义时间格式
                Visible = true,
                ShowUpDown = true,
                Value = DateTime.Now
            };
            // 查询按钮
            btnQuery = new Button
            {
                Text = "查询",
                Location = new Point(370, 40),
                Width = 100
            };
            // 导出按钮
            exportBtn = new Button
            {
                Text = "导出",
                Location = new Point(480, 40),
                Width = 100
            };
            exportBtn.Click += exportBtn_Click;
            // 设置按钮
            setBtn = new Button
            {
                Text = "设置",
                Location = new Point(590, 40),
                Width = 100
            };
            setBtn.Click += setBtn_Click;
            // 上一页按钮
            btnLastPage = new Button
            {
                Text = "上一页",
                Width = 100
            };
            btnLastPage.Click += btnPrevious_Click;
            // 下一页按钮
            btnNextPage = new Button
            {
                Text = "下一页",
                Width = 100
            };
            btnNextPage.Click += btnNext_Click;
            txtPage = new Label
            {
                Location = new Point(30, 40),
                Text = "分页",
                Size = new Size(120, 20),
                TextAlign = ContentAlignment.MiddleCenter
            };
            txtPage.Dock = DockStyle.None;      // 禁用停靠
            txtPage.Anchor = AnchorStyles.None; // 禁用锚定
            panel.Controls.AddRange(new Control[] { btnNextPage, btnLastPage, txtPage });

            // 数据表格
            dataGridView1 = new DataGridView
            {
                Location = new Point(20, 75),
                Size = new Size(760, 320),
                Anchor = AnchorStyles.Left | AnchorStyles.Right | AnchorStyles.Top | AnchorStyles.Bottom,
                AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill
            };
            dataGridView1.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dataGridView1.RowHeadersVisible = false;
            dataGridView1.AllowUserToAddRows = false;
            dataGridView1.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D;
            //this.dataGridView1.BorderStyle = BorderStyle.Fixed3D;
            this.dataGridView1.BackgroundColor = Color.White;
            btnQuery.Click += BtnQuery_Click;
            Console.WriteLine(this.Bottom);
            Controls.AddRange(new Control[] { setBtn, dateText, exportBtn, dtp, dtp2, flag3, jianText, startJianNo, endJianNo, flag2, meterNumText, startMeterNum, endMeterNum, btnQuery, dataGridView1, flag, panel });
            this.Controls.SetChildIndex(panel, 0);
            dataGridView1.BringToFront();
        }

        public Form1()
        {
            InitializeComponent();
            //初始化UI
            SetupUI();
        }

        /// <summary>
        /// 窗体初始化加载
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Form1_Load(object sender, EventArgs e)
        {
            //设置小流量为空的情况
            setAvgError3();
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void BtnQuery_Click(object sender, EventArgs e)
        {
            var sql = new StringBuilder("SELECT id,ROW_NUMBER() OVER(ORDER BY meas_date desc) AS RowNum,meter_num,qiangjianno,meter_type,reglt_code,meter_pos,Round(avg_err1,2) Qmax,Round(avg_err2,2) avg_err2,Round(avg_err3,2) Qmin,Round(pres_loss1,2) pres_loss1,conclusion,Round(atm_pres,2) atm_pres,Round(atm_temp,2) atm_temp,Round(atm_humi,2) atm_humi,meas_date FROM tab_measured_default where 1=1");
            var parameters = new List<SqlParameter>();
            //string sql = "SELECT ROW_NUMBER() OVER(ORDER BY meas_date desc) AS RowNum,meter_num,qiangjianno,meter_type,reglt_code,meter_pos,Round(avg_err1,2) Qmax,Round(avg_err2,2) avg_err2,Round(avg_err3,2) Qmin,Round(pres_loss1,2) pres_loss1,conclusion,Round(atm_pres,2) atm_pres,Round(atm_temp,2) atm_temp,Round(atm_humi,2) atm_humi,meas_date FROM tab_measured_default" +
            //     " where meter_num between @startMeterNum and @endMeterNum  and qiangjianno between @startQiangJianNo and @endQiangJianNo and  meas_date between @startMeasDate and @endMeasDate";
            try
            {
                log4net.Config.XmlConfigurator.Configure();
                using (conn = new SqlConnection(connStr))
                {
                    if (string.IsNullOrEmpty(startMeterNum.Text) && !string.IsNullOrEmpty(endMeterNum.Text))
                    {
                        sql.Append(" AND meter_num between @startMeterNum and @endMeterNum");
                        parameters.Add(new SqlParameter("@startMeterNum", "1"));
                        parameters.Add(new SqlParameter("@endMeterNum", endMeterNum.Text));
                    }
                    else if (!string.IsNullOrEmpty(startMeterNum.Text) && string.IsNullOrEmpty(endMeterNum.Text))
                    {
                        sql.Append(" AND meter_num between @startMeterNum and @endMeterNum");
                        parameters.Add(new SqlParameter("@startMeterNum", startMeterNum.Text));
                        parameters.Add(new SqlParameter("@endMeterNum", "9999999999999999"));
                    }
                    else if (!string.IsNullOrEmpty(startMeterNum.Text) && !string.IsNullOrEmpty(endMeterNum.Text))
                    {
                        sql.Append(" AND meter_num between @startMeterNum and @endMeterNum");
                        parameters.Add(new SqlParameter("@startMeterNum", startMeterNum.Text));
                        parameters.Add(new SqlParameter("@endMeterNum", endMeterNum.Text));
                    }
                    //强检编号
                    if (string.IsNullOrEmpty(startJianNo.Text) && !string.IsNullOrEmpty(endJianNo.Text))
                    {
                        sql.Append(" AND qiangjianno between @startQiangJianNo and @endQiangJianNo");
                        parameters.Add(new SqlParameter("@startQiangJianNo", "1"));
                        parameters.Add(new SqlParameter("@endQiangJianNo", endJianNo.Text));
                    }
                    else if (!string.IsNullOrEmpty(startJianNo.Text) && string.IsNullOrEmpty(endJianNo.Text))
                    {
                        sql.Append(" AND qiangjianno between @startQiangJianNo and @endQiangJianNo");
                        parameters.Add(new SqlParameter("@startQiangJianNo", startJianNo.Text));
                        parameters.Add(new SqlParameter("@endQiangJianNo", "9999999999999999"));
                    }
                    else if (!string.IsNullOrEmpty(startJianNo.Text) && !string.IsNullOrEmpty(endJianNo.Text))
                    {
                        sql.Append(" AND qiangjianno between @startQiangJianNo and @endQiangJianNo");
                        parameters.Add(new SqlParameter("@startQiangJianNo", startJianNo.Text));
                        parameters.Add(new SqlParameter("@endQiangJianNo", endJianNo.Text));
                    }
                    //检验时间
                    if (dtp.Value == null && dtp2.Value != null)
                    {
                        sql.Append(" AND meas_date between @startMeasDate and @endMeasDate");
                        parameters.Add(new SqlParameter("@startMeasDate", "1900-01-01 00:00:00"));
                        parameters.Add(new SqlParameter("@endMeasDate", dtp2.Value.ToString("yyyy-MM-dd") + " 23:59:59"));
                    }
                    else if (dtp.Value != null && dtp2.Value == null)
                    {
                        sql.Append(" AND meas_date between @startMeasDate and @endMeasDate");
                        parameters.Add(new SqlParameter("@startMeasDate", dtp.Value.ToString("yyyy-MM-dd") + " 00:00:00"));
                        parameters.Add(new SqlParameter("@endMeasDate", "2999-01-01 23:59:59"));
                    }
                    else if (dtp.Value != null && dtp2.Value != null)
                    {
                        sql.Append(" AND meas_date between @startMeasDate and @endMeasDate");
                        parameters.Add(new SqlParameter("@startMeasDate", dtp.Value.ToString("yyyy-MM-dd") + " 00:00:00"));
                        parameters.Add(new SqlParameter("@endMeasDate", dtp2.Value.ToString("yyyy-MM-dd") + " 23:59:59"));
                    }
                    SqlCommand cmd = new SqlCommand(sql.ToString(), conn);
                    //cmd.Parameters.AddWithValue("@startMeterNum", startMeterNum.Text);
                    //cmd.Parameters.AddWithValue("@endMeterNum", endMeterNum.Text);
                    //cmd.Parameters.AddWithValue("@startQiangJianNo", startJianNo.Text);
                    //cmd.Parameters.AddWithValue("@endQiangJianNo", endJianNo.Text);
                    //cmd.Parameters.AddWithValue("@startMeasDate", dtp.Value.ToString("yyyy-MM-dd") + " 00:00:00");
                    //cmd.Parameters.AddWithValue("@endMeasDate", dtp2.Value.ToString("yyyy-MM-dd") + " 23:59:59");
                    cmd.Parameters.AddRange(parameters.ToArray());
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    dt = new DataTable();
                    adapter.Fill(dt);
                    dt.Columns.Add("结论", typeof(string));
                    dt.Columns.Add("型号", typeof(string));
                    foreach (DataRow row in dt.Rows)
                    {
                        // 处理每一行数据，例如打印出来或进行计算
                        string s = row["meter_type"].ToString().Substring(6, 1);
                        log.Info("测试方案："+row["meter_type"].ToString());
                        row["meter_type"] = row["meter_type"].ToString().Trim().Substring(5);
                        log.Info("结论：" + (row["conclusion"].ToString().Equals(0) ? "不合格" : "合格"));
                        row["结论"] = row["conclusion"].ToString().Equals(0) ? "不合格" : "合格";
                        if (!string.IsNullOrEmpty(row["Qmax"].ToString()))
                        {
                            row["Qmax"] = Convert.ToDecimal(row["Qmax"].ToString()).ToString();
                        }
                        if (!string.IsNullOrEmpty(row["avg_err2"].ToString()))
                        {
                            row["avg_err2"] = Convert.ToDecimal(row["avg_err2"].ToString()).ToString();
                        }
                        if (!string.IsNullOrEmpty(row["Qmin"].ToString()))
                        {
                            row["Qmin"] = Convert.ToDecimal(row["Qmin"].ToString()).ToString();
                        }
                        if (s.Equals("1"))
                        {
                            row["型号"] = "1.6";
                        }
                        else if (s.Equals("2"))
                        {
                            row["型号"] = "2.5";
                        }
                        else
                        {
                            row["型号"] = "4";
                        }
                        int spaceIndex = row["meas_date"].ToString().IndexOf(' ');
                        log.Info("检验日期：" + row["meas_date"].ToString());
                        row["meas_date"] = row["meas_date"].ToString().Substring(0, spaceIndex);
                    }
                    dt.Columns.Remove("conclusion");
                    dt.Columns["型号"].SetOrdinal(4);
                    dt.Columns["meter_num"].ColumnName = "出厂表号";
                    dt.Columns["qiangjianno"].ColumnName = "检验编号";
                    dt.Columns["meter_type"].ColumnName = "测试方案";
                    dt.Columns["reglt_code"].ColumnName = "设备编号";
                    dt.Columns["meter_pos"].ColumnName = "表位";
                    dt.Columns["Qmax"].ColumnName = "Qmax(%)";
                    dt.Columns["avg_err2"].ColumnName = "0.2Qmax(%)";
                    dt.Columns["Qmin"].ColumnName = "Qmin(%)";
                    dt.Columns["pres_loss1"].ColumnName = "压损(Pa)";
                    dt.Columns["atm_pres"].ColumnName = "环境压力(kPa)";
                    dt.Columns["atm_temp"].ColumnName = "环境温度(℃)";
                    dt.Columns["atm_humi"].ColumnName = "环境湿度(‰PH)";
                    dt.Columns["meas_date"].ColumnName = "检验日期";
                    dt.Columns["RowNum"].ColumnName = "序列号";
                    totalPages = dt.Rows.Count;
                    LoadPage(currentPage);
                    dt.Columns.Remove("id");
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show($"数据库错误: {ex.Message}");
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 分页加载
        /// </summary>
        /// <param name="page"></param>
        private void LoadPage(int page)
        {
            int startIndex = (currentPage - 1) * pageSize;
            int endIndex = Math.Min(startIndex + pageSize, dt.Rows.Count);
            DataView view = new DataView(dt);
            view.RowFilter = $"序列号 > {startIndex} AND 序列号 <= {endIndex}";
            txtPage.Text = "第" + currentPage + "页/共" + totalPages + "条";
            dataGridView1.DataSource = view;
        }
        /// <summary>
        /// 下一页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnNext_Click(object sender, EventArgs e)
        {
            if (currentPage * pageSize < dt.Rows.Count)
            {
                currentPage++;
                LoadPage(currentPage);
                txtPage.Text = "第" + currentPage + "页/共" + totalPages + "条";
            }
        }

        /// <summary>
        /// 上一页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnPrevious_Click(object sender, EventArgs e)
        {
            if (currentPage > 1)
            {
                currentPage--;
                LoadPage(currentPage);
                txtPage.Text = "第" + currentPage + "页/共" + totalPages + "条";
            }
        }

        /// <summary>
        /// 导出按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void exportBtn_Click(object sender, EventArgs e)
        {
            var sql = new StringBuilder("SELECT id,ROW_NUMBER() OVER(ORDER BY meas_date desc) AS RowNum,meter_num,qiangjianno,meter_type,reglt_code,meter_pos,Round(avg_err1,2) Qmax,Round(avg_err2,2) avg_err2,Round(avg_err3,2) Qmin,Round(pres_loss1,2) pres_loss1,conclusion,Round(atm_pres,2) atm_pres,Round(atm_temp,2) atm_temp,Round(atm_humi,2) atm_humi,meas_date FROM tab_measured_default where 1=1");
            var parameters = new List<SqlParameter>();
            //string sql = "SELECT ROW_NUMBER() OVER(ORDER BY meas_date desc) AS RowNum, meter_num, qiangjianno, meter_type, reglt_code, meter_pos, Round(avg_err1, 2) Qmax,Round(avg_err2, 2) avg_err2,Round(avg_err3, 2) Qmin,Round(pres_loss1, 2) pres_loss1,conclusion,Round(atm_pres, 2) atm_pres,Round(atm_temp, 2) atm_temp,Round(atm_humi, 2) atm_humi,meas_date FROM tab_measured_default" +
            //    " where meter_num between @startMeterNum and @endMeterNum  and qiangjianno between @startQiangJianNo and @endQiangJianNo and  meas_date between @startMeasDate and @endMeasDate";
            try
            {
                using (conn = new SqlConnection(connStr))
                {
                    if(string.IsNullOrEmpty(startMeterNum.Text) && !string.IsNullOrEmpty(endMeterNum.Text)) {
                        sql.Append(" AND meter_num between @startMeterNum and @endMeterNum");
                        parameters.Add(new SqlParameter("@startMeterNum", "1"));
                        parameters.Add(new SqlParameter("@endMeterNum", endMeterNum.Text));
                    }
                    else if (!string.IsNullOrEmpty(startMeterNum.Text) && string.IsNullOrEmpty(endMeterNum.Text)) {
                        sql.Append(" AND meter_num between @startMeterNum and @endMeterNum");
                        parameters.Add(new SqlParameter("@startMeterNum", startMeterNum.Text));
                        parameters.Add(new SqlParameter("@endMeterNum", "9999999999999999"));
                    }
                    else if (!string.IsNullOrEmpty(startMeterNum.Text) && !string.IsNullOrEmpty(endMeterNum.Text))
                    {
                        sql.Append(" AND meter_num between @startMeterNum and @endMeterNum");
                        parameters.Add(new SqlParameter("@startMeterNum", startMeterNum.Text));
                        parameters.Add(new SqlParameter("@endMeterNum", endMeterNum.Text));
                    }
                    //强检编号
                    if (string.IsNullOrEmpty(startJianNo.Text) && !string.IsNullOrEmpty(endJianNo.Text))
                    {
                        sql.Append(" AND qiangjianno between @startQiangJianNo and @endQiangJianNo");
                        parameters.Add(new SqlParameter("@startQiangJianNo", "1"));
                        parameters.Add(new SqlParameter("@endQiangJianNo", endJianNo.Text));
                    }
                    else if (!string.IsNullOrEmpty(startJianNo.Text) && string.IsNullOrEmpty(endJianNo.Text))
                    {
                        sql.Append(" AND qiangjianno between @startQiangJianNo and @endQiangJianNo");
                        parameters.Add(new SqlParameter("@startQiangJianNo", startJianNo.Text));
                        parameters.Add(new SqlParameter("@endQiangJianNo", "9999999999999999"));
                    }
                    else if (!string.IsNullOrEmpty(startJianNo.Text) && !string.IsNullOrEmpty(endJianNo.Text))
                    {
                        sql.Append(" AND qiangjianno between @startQiangJianNo and @endQiangJianNo");
                        parameters.Add(new SqlParameter("@startQiangJianNo", startJianNo.Text));
                        parameters.Add(new SqlParameter("@endQiangJianNo", endJianNo.Text));
                    }
                    //检验时间
                    if (dtp.Value == null && dtp2.Value != null)
                    {
                        sql.Append(" AND meas_date between @startMeasDate and @endMeasDate");
                        parameters.Add(new SqlParameter("@startMeasDate", "1900-01-01 00:00:00"));
                        parameters.Add(new SqlParameter("@endMeasDate", dtp2.Value.ToString("yyyy-MM-dd") + " 23:59:59"));
                    }
                    else if (dtp.Value != null && dtp2.Value == null)
                    {
                        sql.Append(" AND meas_date between @startMeasDate and @endMeasDate");
                        parameters.Add(new SqlParameter("@startMeasDate", dtp.Value.ToString("yyyy-MM-dd") + " 00:00:00"));
                        parameters.Add(new SqlParameter("@endMeasDate", "2999-01-01 23:59:59"));
                    }
                    else if (dtp.Value != null && dtp2.Value != null)
                    {
                        sql.Append(" AND meas_date between @startMeasDate and @endMeasDate");
                        parameters.Add(new SqlParameter("@startMeasDate", dtp.Value.ToString("yyyy-MM-dd") + " 00:00:00"));
                        parameters.Add(new SqlParameter("@endMeasDate", dtp2.Value.ToString("yyyy-MM-dd") + " 23:59:59"));
                    }
                    //cmd.Parameters.AddWithValue("@startMeterNum", startMeterNum.Text);
                    //cmd.Parameters.AddWithValue("@endMeterNum", endMeterNum.Text);
                    //cmd.Parameters.AddWithValue("@startQiangJianNo", startJianNo.Text);
                    //cmd.Parameters.AddWithValue("@endQiangJianNo", endJianNo.Text);
                    //cmd.Parameters.AddWithValue("@startMeasDate", dtp.Value.ToString("yyyy-MM-dd") + " 00:00:00");
                    //cmd.Parameters.AddWithValue("@endMeasDate", dtp2.Value.ToString("yyyy-MM-dd") + " 23:59:59");
                    SqlCommand cmd = new SqlCommand(sql.ToString(), conn);
                    cmd.Parameters.AddRange(parameters.ToArray());
                    Console.WriteLine(cmd.CommandText);
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    dt = new DataTable();
                    adapter.Fill(dt);
                    dt.Columns.Add("结论", typeof(string));
                    dt.Columns.Add("型号", typeof(string));
                    foreach (DataRow row in dt.Rows)
                    {
                        // 处理每一行数据，例如打印出来或进行计算
                        log.Info("测试方案：" + row["meter_type"].ToString());
                        string s = row["meter_type"].ToString().Substring(6, 1);
                        row["meter_type"] = row["meter_type"].ToString().Trim().Substring(5);
                        log.Info("结论：" + (row["conclusion"].ToString().Equals(0) ? "不合格" : "合格"));
                        row["结论"] = row["conclusion"].ToString().Equals(0) ? "不合格" : "合格";
                        if (!string.IsNullOrEmpty(row["Qmax"].ToString()))
                        {
                            row["Qmax"] = Convert.ToDecimal(row["Qmax"].ToString()).ToString();
                        }
                        if (!string.IsNullOrEmpty(row["avg_err2"].ToString()))
                        {
                            row["avg_err2"] = Convert.ToDecimal(row["avg_err2"].ToString()).ToString();
                        }
                        if (!string.IsNullOrEmpty(row["Qmin"].ToString()))
                        {
                            row["Qmin"] = Convert.ToDecimal(row["Qmin"].ToString()).ToString();
                        }
                        if (s.Equals("1"))
                        {
                            row["型号"] = "1.6";
                        }
                        else if (s.Equals("2"))
                        {
                            row["型号"] = "2.5";
                        }
                        else
                        {
                            row["型号"] = "4";
                        }
                        int spaceIndex = row["meas_date"].ToString().IndexOf(' ');
                        log.Info("检验日期：" + row["meas_date"].ToString());
                        row["meas_date"] = row["meas_date"].ToString().Substring(0, spaceIndex);
                    }
                    dt.Columns.Remove("conclusion");
                    dt.Columns["型号"].SetOrdinal(4);
                    dt.Columns["meter_num"].ColumnName = "出厂表号";
                    dt.Columns["qiangjianno"].ColumnName = "检验编号";
                    dt.Columns["meter_type"].ColumnName = "测试方案";
                    dt.Columns["reglt_code"].ColumnName = "设备编号";
                    dt.Columns["meter_pos"].ColumnName = "表位";
                    dt.Columns["Qmax"].ColumnName = "Qmax(%)";
                    dt.Columns["avg_err2"].ColumnName = "0.2Qmax(%)";
                    dt.Columns["Qmin"].ColumnName = "Qmin(%)";
                    dt.Columns["pres_loss1"].ColumnName = "压损(Pa)";
                    dt.Columns["atm_pres"].ColumnName = "环境压力(kPa)";
                    dt.Columns["atm_temp"].ColumnName = "环境温度(℃)";
                    dt.Columns["atm_humi"].ColumnName = "环境湿度(‰PH)";
                    dt.Columns["meas_date"].ColumnName = "检验日期";
                    dt.Columns["RowNum"].ColumnName = "序列号";
                    dataGridView1.DataSource = dt;
                    ExportToExcel(dataGridView1);
                    LoadPage(currentPage);
                    dt.Columns.Remove("id");
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show($"数据库错误: {ex.Message}");
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="dgv"></param>
        private void ExportToExcel(DataGridView dgv)
        {
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            // 创建表头
            IRow headerRow = sheet.CreateRow(0);
            for (int i = 0; i < dgv.Columns.Count; i++)
            {
                headerRow.CreateCell(i).SetCellValue(dgv.Columns[i].HeaderText);
            }
            // 填充数据
            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dgv.Columns.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(dgv.Rows[i].Cells[j].Value?.ToString());
                }
            }
            // 保存文件
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.Filter = "Excel文件|*.xls";
            if (saveDialog.ShowDialog() == DialogResult.OK)
            {
                using (FileStream fs = new FileStream(saveDialog.FileName, FileMode.Create))
                {
                    workbook.Write(fs);
                }
            }
        }

        /// <summary>
        /// 设置
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void setBtn_Click(object sender, EventArgs e)
        {
            setAvgError3();
        }

        /// <summary>
        /// 设置小流量等于空并查询当天数据
        /// </summary>
        private void setAvgError3()
        {
            string sql = "SELECT id,ROW_NUMBER() OVER(ORDER BY meas_date desc) AS RowNum,meter_num,qiangjianno,meter_type,reglt_code,meter_pos,Round(avg_err1,2) Qmax,Round(avg_err2,2) avg_err2,Round(avg_err3,2) Qmin,Round(pres_loss1,2) pres_loss1,conclusion,Round(atm_pres,2) atm_pres,Round(atm_temp,2) atm_temp,Round(atm_humi,2) atm_humi,meas_date FROM tab_measured_default " +
                            " where CONVERT(date, meas_date) = CONVERT(date, GETDATE())";
            try
            {
                using (conn = new SqlConnection(connStr))
                {
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    dt = new DataTable();
                    adapter.Fill(dt);
                    dt.Columns.Add("结论", typeof(string));
                    dt.Columns.Add("型号", typeof(string));
                    foreach (DataRow row in dt.Rows)
                    {
                        // 处理每一行数据，例如打印出来或进行计算
                        log.Info("测试方案：" + row["meter_type"].ToString());
                        string s = row["meter_type"].ToString().Substring(6, 1);
                        row["meter_type"] = row["meter_type"].ToString().Trim().Substring(5);
                        log.Info("结论：" + (row["conclusion"].ToString().Equals(0) ? "不合格" : "合格"));
                        row["结论"] = row["conclusion"].ToString().Equals(0) ? "不合格" : "合格";
                        if (!string.IsNullOrEmpty(row["Qmax"].ToString()))
                        {
                            row["Qmax"] = Convert.ToDecimal(row["Qmax"].ToString()).ToString();
                        }
                        if (!string.IsNullOrEmpty(row["avg_err2"].ToString()))
                        {
                            row["avg_err2"] = Convert.ToDecimal(row["avg_err2"].ToString()).ToString();
                        }
                        if (!string.IsNullOrEmpty(row["Qmin"].ToString()))
                        {
                            row["Qmin"] = Convert.ToDecimal(row["Qmin"].ToString()).ToString();
                        }
                        if (s.Equals("1"))
                        {
                            row["型号"] = "1.6";
                        }
                        else if (s.Equals("2"))
                        {
                            row["型号"] = "2.5";
                        }
                        else
                        {
                            row["型号"] = "4";
                        }
                        int spaceIndex = row["meas_date"].ToString().IndexOf(' ');
                        log.Info("检验日期：" + row["meas_date"].ToString());
                        if (row["Qmin"].ToString() == null || row["Qmin"].ToString().Equals(""))
                        {
                            Random random = new Random(Guid.NewGuid().GetHashCode());
                            //double randomValue = Math.Round(random.NextDouble() * (0.5 - (-0.2)) + (-0.2), 2);
                            double randomValue = -0.2 + (0.5 - (-0.2)) * random.NextDouble();
                            row["Qmin"] = Math.Round(randomValue, 2);
                        }
                        row["meas_date"] = row["meas_date"].ToString().Substring(0, spaceIndex);
                        sql = @"UPDATE tab_measured_default SET avg_err3 = @Qmin WHERE id = @id";
                        using (cmd = new SqlCommand(sql, conn))
                        {
                            conn.Open(); // 必须显式调用Open()
                            cmd.Parameters.AddWithValue("@Qmin", row["Qmin"].ToString());
                            cmd.Parameters.AddWithValue("@id", row["id"].ToString());
                            int result = cmd.ExecuteNonQuery();
                            if (result <= 0) MessageBox.Show("未找到匹配记录");
                            conn.Close();
                        }
                    }
                    dt.Columns.Remove("conclusion");
                    dt.Columns["型号"].SetOrdinal(4);
                    dt.Columns["meter_num"].ColumnName = "出厂表号";
                    dt.Columns["qiangjianno"].ColumnName = "检验编号";
                    dt.Columns["meter_type"].ColumnName = "测试方案";
                    dt.Columns["reglt_code"].ColumnName = "设备编号";
                    dt.Columns["meter_pos"].ColumnName = "表位";
                    dt.Columns["Qmax"].ColumnName = "Qmax(%)";
                    dt.Columns["avg_err2"].ColumnName = "0.2Qmax(%)";
                    dt.Columns["Qmin"].ColumnName = "Qmin(%)";
                    dt.Columns["pres_loss1"].ColumnName = "压损(Pa)";
                    dt.Columns["atm_pres"].ColumnName = "环境压力(kPa)";
                    dt.Columns["atm_temp"].ColumnName = "环境温度(℃)";
                    dt.Columns["atm_humi"].ColumnName = "环境湿度(‰PH)";
                    dt.Columns["meas_date"].ColumnName = "检验日期";
                    dt.Columns["RowNum"].ColumnName = "序列号";
                    totalPages = dt.Rows.Count;
                    LoadPage(currentPage);
                    dt.Columns.Remove("id");
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show($"数据库错误: {ex.Message}");
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
